# -*- coding:utf-8 -*—
#第一步：调用pandas包
import time

import pandas as pd
import os
from pandas import DataFrame




def sumExcel(excelPath, filedName,sumFieldName):
    # 第二步：读入文件
    df = pd.read_excel(io=excelPath)
    # 第三步：获取class列表并去重
    # class_list = list(df[filedName].drop_duplicates())
    class_list = list(df[filedName].unique())
    exportPath = os.path.split(os.path.abspath(excelPath))[0]
    filename = os.path.split(os.path.abspath(excelPath))[1]
    # 第四步：按照类别分文件存放数据
    resultlist=[]
    for i in class_list:
        result = {}
        iris1:DataFrame= df[df[filedName] == i]
        # outpath = exportPath + '%s.xlsx' % (i)
        # iris1.to_excel(outpath)
        result[filedName]=i
        # result[expName]=df[expName]
        result[sumFieldName]=iris1[sumFieldName].mean()
        resultlist.append(result)
    print(resultlist)
    # 将字典列表转换为DataFrame
    pf = pd.DataFrame(list(resultlist))
    # 指定字段顺序
    order = [filedName, sumFieldName]
    pf = pf[order]
    pf.sort_values(by=filedName, inplace=True, ascending=False)
    now_time = time.strftime('%Y-%m-%d-%H-%M-%S', time.localtime())
    outname = "./python统计结果" + now_time + filename
    print(outname)
    outpath = exportPath + outname
    # outpath = exportPath + './python统计结果.xlsx'
    pf.to_excel(outpath)


if __name__ == '__main__':
    # todo：欧洲

    # path=r"F:\data\水电流域\2022.09.26预报数据对比\距平降雨出图\降雨和温度多年统计(1).xlsx"
    # path=r"E:\宁乡统计\所有道路.xlsx"
    path=r"E:\宁乡统计\52010000.xlsx"

    # filedName="RVCD"
    # filedName="MONTH"
    filedName="要素编码"
    sumFieldName="pre"
    expName="NAME"
    # todo 分割excel
    # splitExcel(path,filedName)
    # todo 对每列excel求和
    splitExcel(path,expName,sumFieldName)